﻿#include "inform.h"
#include "ui_inform.h"


#include <QDomDocument>
#include <QDomProcessingInstruction>
#include <QDomElement>

#include <QLineEdit>
#include <QString>
#include <QStringList>
#include <QFile>
#include <QFileDialog>
#include <QTextEdit>
#include <QTextStream>
#include <QTableWidget>
#include <QXmlStreamWriter>

Inform::Inform(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Inform)
{
    ui->setupUi(this);

    ui->tuixiu->setSelectionBehavior ( QAbstractItemView::SelectRows);
    ui->tuixiu->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tuixiu->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->rzsj->setSelectionBehavior ( QAbstractItemView::SelectRows);
    ui->rzsj->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->rzsj->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

Inform::~Inform()
{
    delete ui;
}



void Inform::on_ReadTrans_clicked()
{

    ui->tuixiu->setRowCount(0);
    ui->transinfo->setRowCount(0);
    //退休提醒
    QSqlQuery query;

    QSqlQuery q;

    QString male=QString("select CadreName,CadreInfo.IDNum,date_add(Birth,INTERVAL 60 YEAR) ,CONCAT(DATEDIFF(date_add(Birth,INTERVAL 60 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='1' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and DATEDIFF(date_add(Birth,INTERVAL 60 YEAR) ,NOW())<'365'");
    QString female=QString("select CadreName,CadreInfo.IDNum,date_add(Birth,INTERVAL 55 YEAR) ,CONCAT(DATEDIFF(date_add(Birth,INTERVAL 55 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='0' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and DATEDIFF(date_add(Birth,INTERVAL 55 YEAR) ,NOW())<'365'");
    q.exec(male);
    qDebug()<<male<<"\n"<<female;
    while(q.next())
    {
        int rowcount=ui->tuixiu->rowCount();
        ui->tuixiu->insertRow(rowcount);
        ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("退休"));

        QString a=q.value(0).toString();
        ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

        QString b=q.value(2).toString();
        ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

        QString c=q.value(3).toString();
        ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


        QString d=q.value(4).toString();
        ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

        //QString e=q.value(5).toString();
        ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("男"));

        QString f=q.value(5).toString();
        ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=q.value(7).toString();
        ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

        QString h=q.value(8).toString();
        ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));

        QString i=q.value(1).toString();
        ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

        QString j=q.value(9).toString();
        ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

        QString k=q.value(10).toString();
        ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));
    }

    query.exec(female);
    while( query.next())
    {

        int rowcount=ui->tuixiu->rowCount();
        ui->tuixiu->insertRow(rowcount);

        ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("退休"));

        QString a=query.value(0).toString();
        ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

        QString i=query.value(1).toString();
        ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

        QString b=query.value(2).toString();
        ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

        QString c=query.value(3).toString();
        ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


        QString d=query.value(4).toString();
        ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

        //QString e=query.value(5).toString();
        ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("女"));

        QString f=query.value(5).toString();
        ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=query.value(7).toString();
        ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

        QString h=query.value(8).toString();
        ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));



        QString j=query.value(9).toString();
        ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

        QString k=query.value(10).toString();
        ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));

    }

    //转正提醒

    QSqlQuery zzm;

    QSqlQuery zzf;

    QString zzmale=QString("select CadreName,CadreInfo.IDNum,date_add(JoinWorkTime,INTERVAL 1 YEAR) ,CONCAT(DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='1' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and CadreInfo.RankID='199' and DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW())<'90'");
    QString zzfemale=QString("select CadreName,CadreInfo.IDNum,date_add(JoinWorkTime,INTERVAL 1 YEAR) ,CONCAT(DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='0' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and CadreInfo.RankID='199' and DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW())<'90'");
    zzm.exec(zzmale);
    while(zzm.next())
    {
        int rowcount=ui->tuixiu->rowCount();
        ui->tuixiu->insertRow(rowcount);
        ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("转正"));

        QString a=zzm.value(0).toString();
        ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

        QString b=zzm.value(2).toString();
        ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

        QString c=zzm.value(3).toString();
        ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


        QString d=zzm.value(4).toString();
        ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

        //QString e=q.value(5).toString();
        ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("男"));

        QString f=zzm.value(5).toString();
        ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=zzm.value(7).toString();
        ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

        QString h=zzm.value(8).toString();
        ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));

        QString i=zzm.value(1).toString();
        ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

        QString j=zzm.value(9).toString();
        ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

        QString k=zzm.value(10).toString();
        ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));
    }

    zzf.exec(zzfemale);
    while( zzf.next())
    {

        int rowcount=ui->tuixiu->rowCount();
        ui->tuixiu->insertRow(rowcount);

        ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("转正"));

        QString a=zzf.value(0).toString();
        ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

        QString i=zzf.value(1).toString();
        ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

        QString b=zzf.value(2).toString();
        ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

        QString c=zzf.value(3).toString();
        ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


        QString d=zzf.value(4).toString();
        ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

        //QString e=query.value(5).toString();
        ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("女"));

        QString f=zzf.value(5).toString();
        ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=zzf.value(7).toString();
        ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

        QString h=zzf.value(8).toString();
        ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));



        QString j=zzf.value(9).toString();
        ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

        QString k=zzf.value(10).toString();
        ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));

    }










    int a=ui->tuixiu->rowCount();
    ui->txnum->setText(QString::number(a));
    if (a==0) {
        QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("未能查询相关提醒信息!"));

    }


}

void Inform::on_ReadTime_clicked()
{
    ui->rzsj->setRowCount(0);
    ui->timeinfo->setRowCount(0);

    //正职领导提醒
    QSqlQuery query;
    QString female=QString("select CadreName,Sex,CadreInfo.IDNum,Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),unitname,PostName, PostTime,RankName,PostCategoryCode.PostCategory,CONCAT(TIMESTAMPDIFF(YEAR,PostTime,NOW()), '年') from CadreInfo,Post,unit,RankCode,PostCategoryCode WHERE  CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and Post.PostCategory=PostCategoryCode.PostCategoryID and TIMESTAMPDIFF(YEAR,PostTime,NOW())>'5' and (Post.PostCategory='21' or Post.PostCategory='11')");


    query.exec(female);
    while( query.next())
    {

        int rowcount=ui->rzsj->rowCount();
        ui->rzsj->insertRow(rowcount);
        QString a=query.value(0).toString();
        ui->rzsj->setItem(rowcount,0,new QTableWidgetItem(a));

        QString i=query.value(1).toString();

        if (i.toInt(0)==1) {
            ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("男"));
        }
        else{
            ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("女"));

        }


        QString b=query.value(2).toString();
        ui->rzsj->setItem(rowcount,2,new QTableWidgetItem(b));

        QString c=query.value(3).toString();
        ui->rzsj->setItem(rowcount,3,new QTableWidgetItem(c));


        QString d=query.value(4).toString();
        ui->rzsj->setItem(rowcount,4,new QTableWidgetItem(d));

        QString e=query.value(5).toString();
        ui->rzsj->setItem(rowcount,5,new QTableWidgetItem(e));

        QString f=query.value(6).toString();
        ui->rzsj->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=query.value(7).toString();
        ui->rzsj->setItem(rowcount,7,new QTableWidgetItem(g));

        QString h=query.value(8).toString();
        ui->rzsj->setItem(rowcount,8,new QTableWidgetItem(h));

        QString j=query.value(9).toString();
        ui->rzsj->setItem(rowcount,9,new QTableWidgetItem(j));

        QString k=query.value(10).toString();
        ui->rzsj->setItem(rowcount,10,new QTableWidgetItem(k));

    }

    //副职领导提醒

    QSqlQuery fum;//男副职
    QSqlQuery fuf;//女副职

    QString funan=QString("select CadreName,Sex,CadreInfo.IDNum,Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),unitname,PostName, PostTime,RankName,PostCategoryCode.PostCategory,CONCAT(TIMESTAMPDIFF(YEAR,PostTime,NOW()), '年')from CadreInfo,Post,unit,RankCode,PostCategoryCode WHERE Sex='1' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and Post.PostCategory=PostCategoryCode.PostCategoryID and TIMESTAMPDIFF(YEAR,PostTime,NOW())>'10' and Post.PostCategory='12' ");
    QString funv=QString("select CadreName,Sex,CadreInfo.IDNum,Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),unitname,PostName, PostTime,RankName,PostCategoryCode.PostCategory,CONCAT(TIMESTAMPDIFF(YEAR,PostTime,NOW()), '年') from CadreInfo,Post,unit,RankCode,PostCategoryCode WHERE Sex='0' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and Post.PostCategory=PostCategoryCode.PostCategoryID and TIMESTAMPDIFF(YEAR,PostTime,NOW())>'10' and Post.PostCategory='12' ");
    fum.exec(funan);
    while(fum.next())
    {
        int rowcount=ui->rzsj->rowCount();
        ui->rzsj->insertRow(rowcount);
        QString a=fum.value(0).toString();
        ui->rzsj->setItem(rowcount,0,new QTableWidgetItem(a));

        //QString i=q.value(1).toString();
        ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("男"));

        QString b=fum.value(2).toString();
        ui->rzsj->setItem(rowcount,2,new QTableWidgetItem(b));

        QString c=fum.value(3).toString();
        ui->rzsj->setItem(rowcount,3,new QTableWidgetItem(c));


        QString d=fum.value(4).toString();
        ui->rzsj->setItem(rowcount,4,new QTableWidgetItem(d));

        QString e=fum.value(5).toString();
        ui->rzsj->setItem(rowcount,5,new QTableWidgetItem(e));

        QString f=fum.value(6).toString();
        ui->rzsj->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=fum.value(7).toString();
        ui->rzsj->setItem(rowcount,7,new QTableWidgetItem(g));

        QString h=fum.value(8).toString();
        ui->rzsj->setItem(rowcount,8,new QTableWidgetItem(h));

        QString j=fum.value(9).toString();
        ui->rzsj->setItem(rowcount,9,new QTableWidgetItem(j));

        QString k=fum.value(10).toString();
        ui->rzsj->setItem(rowcount,10,new QTableWidgetItem(k));
    }

    fuf.exec(funv);
    while( fuf.next())
    {

        int rowcount=ui->rzsj->rowCount();
        ui->rzsj->insertRow(rowcount);
        QString a=fuf.value(0).toString();
        ui->rzsj->setItem(rowcount,0,new QTableWidgetItem(a));

        int i=fuf.value(1).toInt();
        if (i==1) {
            ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("女"));
        }

        if (i==2) {
            ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("男"));

        }

        QString b=fuf.value(2).toString();
        ui->rzsj->setItem(rowcount,2,new QTableWidgetItem(b));

        QString c=fuf.value(3).toString();
        ui->rzsj->setItem(rowcount,3,new QTableWidgetItem(c));

        QString d=fuf.value(4).toString();
        ui->rzsj->setItem(rowcount,4,new QTableWidgetItem(d));

        QString e=fuf.value(5).toString();
        ui->rzsj->setItem(rowcount,5,new QTableWidgetItem(e));

        QString f=fuf.value(6).toString();
        ui->rzsj->setItem(rowcount,6,new QTableWidgetItem(f));

        QString g=fuf.value(7).toString();
        ui->rzsj->setItem(rowcount,7,new QTableWidgetItem(g));

        QString h=fuf.value(8).toString();
        ui->rzsj->setItem(rowcount,8,new QTableWidgetItem(h));

        QString j=fuf.value(9).toString();
        ui->rzsj->setItem(rowcount,9,new QTableWidgetItem(j));

        QString k=fuf.value(10).toString();
        ui->rzsj->setItem(rowcount,10,new QTableWidgetItem(k));

    }

    int row=ui->rzsj->rowCount();
    ui->renshu->setText(QString::number(row));


int r=ui->rzsj->rowCount();
if (r==0) {
    QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("未能查询到结果!"));

}
}


void Inform::on_pushButton_clicked()
{
    QString filepath = QFileDialog::getSaveFileName(this, tr("Save as xml"),QString(), "XML files (*.xml);;");
    if (filepath.isEmpty()) {
        return;
    }
    filepath += ".xml";

    QFile file(filepath);
    if (!file.open(QFile::WriteOnly | QIODevice::Text)) {
        return;
    }


    //创建xml文档对象
    QDomDocument doc;
    //创建xml头部格式 <?xml version='1.0' encoding='utf-8'?>
    QDomProcessingInstruction ins;
    ins = doc.createProcessingInstruction("xml", "version=\'1.0\' encoding=\'utf-8\'");
    //追加元素
    doc.appendChild(ins);

    //根节点元素
    QDomElement root = doc.createElement("转正退休提醒信息");
    doc.appendChild(root);

//    //创建文书内容子节点元素
//    QDomElement docEmt = doc.createElement("");
//    //把节点追加到根节点上
//    root.appendChild(docEmt);

    //文书信息元素
//    QDomElement docText = doc.createElement("转正退休干部信息");
//    QString strText;

//    //选择文书内容
//    int docWIndex = ui->stackedWidget->currentIndex();
//    if(docWIndex == 0)
//    {
//        strText = ui->textEdit->toPlainText();
//    }
//    if(docWIndex == 1)
//    {
//        strText = ui->textEdit_2->toPlainText();
//    }

//    QDomText text = doc.createTextNode(strText);
//    docText.appendChild(text);

//    docEmt.appendChild(docText);

    //附录
//    QDomElement apEmt = doc.createElement("提醒表格");
//    root.appendChild(apEmt);

    int rowc = ui->tuixiu->rowCount();
    for(int i=0;i<rowc;i++)
    {
        //附录
        QDomElement jlEmt = doc.createElement("干部");
        root.appendChild(jlEmt);
        QDomElement type = doc.createElement("类型");
        QDomElement cadreName = doc.createElement("姓名");
        QDomElement idnum = doc.createElement("证件号码");
        QDomElement xiangguan = doc.createElement("相关日期");
        QDomElement shengyu = doc.createElement("剩余时间");
        QDomElement birth = doc.createElement("出生时间");
        QDomElement old = doc.createElement("年龄");
        QDomElement sex = doc.createElement("性别");
        QDomElement unit = doc.createElement("单位名称");
        QDomElement post = doc.createElement("职务");
        QDomElement posttime = doc.createElement("任职时间");
        QDomElement rank = doc.createElement("级别名称");

        if(ui->tuixiu->item(i,0)!=NULL)
            {
            QString typeText = ui->tuixiu->item(i,0)->text();
            //qDebug()<< nameText;
            QDomText text = doc.createTextNode(typeText);
            type.appendChild(text);

        }

        if(ui->tuixiu->item(i,1)!=NULL)
            {
            QString nameText = ui->tuixiu->item(i,1)->text();
            QDomText text = doc.createTextNode(nameText);
            cadreName.appendChild(text);

        }

        if(ui->tuixiu->item(i,2)!=NULL)
            {
            QString idText = ui->tuixiu->item(i,2)->text();
            QDomText text = doc.createTextNode(idText);
            idnum.appendChild(text);

        }

        if(ui->tuixiu->item(i,3)!=NULL)
            {
            QString xgText = ui->tuixiu->item(i,3)->text();
            //qDebug()<< nText;
            QDomText text = doc.createTextNode(xgText);
            xiangguan.appendChild(text);

        }

        if(ui->tuixiu->item(i,4)!=NULL)
            {
            QString syText = ui->tuixiu->item(i,4)->text();
            QDomText text = doc.createTextNode(syText);
            shengyu.appendChild(text);

        }

        if(ui->tuixiu->item(i,5)!=NULL)
            {
            QString birthText = ui->tuixiu->item(i,5)->text();
            QDomText text = doc.createTextNode(birthText);
            birth.appendChild(text);

        }

        if(ui->tuixiu->item(i,6)!=NULL)
            {
            QString oldText = ui->tuixiu->item(i,6)->text();
            QDomText text = doc.createTextNode(oldText);
            old.appendChild(text);

        }

        if(ui->tuixiu->item(i,7)!=NULL)
            {
            QString sexText = ui->tuixiu->item(i,7)->text();
            QDomText text = doc.createTextNode(sexText);
            sex.appendChild(text);

        }

        if(ui->tuixiu->item(i,8)!=NULL)
            {
            QString unitText = ui->tuixiu->item(i,8)->text();
            QDomText text = doc.createTextNode(unitText);
            unit.appendChild(text);

        }

        if(ui->tuixiu->item(i,9)!=NULL)
            {
            QString postText = ui->tuixiu->item(i,9)->text();
            QDomText text = doc.createTextNode(postText);
            post.appendChild(text);

        }
        if(ui->tuixiu->item(i,10)!=NULL)
            {
            QString ptText = ui->tuixiu->item(i,10)->text();
            QDomText text = doc.createTextNode(ptText);
            posttime.appendChild(text);

        }
        if(ui->tuixiu->item(i,11)!=NULL)
            {
            QString rankText = ui->tuixiu->item(i,11)->text();
            QDomText text = doc.createTextNode(rankText);
            rank.appendChild(text);

        }
        jlEmt.appendChild(type);
        jlEmt.appendChild(cadreName);
        jlEmt.appendChild(idnum);
        jlEmt.appendChild(xiangguan);
        jlEmt.appendChild(shengyu);
        jlEmt.appendChild(birth);
        jlEmt.appendChild(old);
        jlEmt.appendChild(sex);
        jlEmt.appendChild(unit);
        jlEmt.appendChild(post);
        jlEmt.appendChild(posttime);
        jlEmt.appendChild(rank);

    }

    //保存
    QTextStream stream(&file); //文本流关联文件
    doc.save(stream, 4); //4 缩进字符
    file.close(); //关闭文件

    QMessageBox::information(this,"提示","导出成功！");







}

void Inform::on_pushButton_7_clicked()
{
    QString filepath = QFileDialog::getSaveFileName(this, tr("Save as xml"),QString(), "XML files (*.xml);;");
    if (filepath.isEmpty()) {
        return;
    }
    filepath += ".xml";

    QFile file(filepath);
    if (!file.open(QFile::WriteOnly | QIODevice::Text)) {
        return;
    }


    //创建xml文档对象
    QDomDocument doc;
    //创建xml头部格式 <?xml version='1.0' encoding='utf-8'?>
    QDomProcessingInstruction ins;
    ins = doc.createProcessingInstruction("xml", "version=\'1.0\' encoding=\'utf-8\'");
    //追加元素
    doc.appendChild(ins);

    //根节点元素
    QDomElement root = doc.createElement("任职时间提醒信息");
    doc.appendChild(root);



    int rowc = ui->rzsj->rowCount();
    for(int i=0;i<rowc;i++)
    {
        //附录
        QDomElement jlEmt = doc.createElement("干部");
        root.appendChild(jlEmt);

        QDomElement cadreName = doc.createElement("干部姓名");
        QDomElement sex = doc.createElement("性别");
        QDomElement idnum = doc.createElement("证件号码");
        QDomElement birth = doc.createElement("出生时间");
        QDomElement old = doc.createElement("年龄");
        QDomElement unit = doc.createElement("单位名称");
        QDomElement post = doc.createElement("单位职务名称");
        QDomElement posttime = doc.createElement("任职时间");
        QDomElement rank = doc.createElement("级别名称");
        QDomElement posttype = doc.createElement("职务类别");
        QDomElement postlong = doc.createElement("同一职位时长");

        if(ui->rzsj->item(i,0)!=NULL)
            {
            QString cadreText = ui->rzsj->item(i,0)->text();
            QDomText text = doc.createTextNode(cadreText);
            cadreName.appendChild(text);

        }

        if(ui->rzsj->item(i,1)!=NULL)
            {
            QString sexText = ui->rzsj->item(i,1)->text();
            QDomText text = doc.createTextNode(sexText);
            sex.appendChild(text);

        }

        if(ui->rzsj->item(i,2)!=NULL)
            {
            QString idText = ui->rzsj->item(i,2)->text();
            QDomText text = doc.createTextNode(idText);
            idnum.appendChild(text);

        }


        if(ui->rzsj->item(i,3)!=NULL)
            {
            QString birthText = ui->tuixiu->item(i,3)->text();
            QDomText text = doc.createTextNode(birthText);
            birth.appendChild(text);

        }

        if(ui->rzsj->item(i,4)!=NULL)
            {
            QString oldText = ui->rzsj->item(i,4)->text();
            QDomText text = doc.createTextNode(oldText);
            old.appendChild(text);

        }

        if(ui->rzsj->item(i,5)!=NULL)
            {
            QString unitText = ui->rzsj->item(i,5)->text();
            QDomText text = doc.createTextNode(unitText);
            unit.appendChild(text);

        }

        if(ui->rzsj->item(i,6)!=NULL)
            {
            QString postText = ui->rzsj->item(i,6)->text();
            QDomText text = doc.createTextNode(postText);
            post.appendChild(text);

        }

        if(ui->rzsj->item(i,7)!=NULL)
            {
            QString ptText = ui->rzsj->item(i,7)->text();
            QDomText text = doc.createTextNode(ptText);
            posttime.appendChild(text);

        }

        if(ui->rzsj->item(i,8)!=NULL)
            {
            QString rankText = ui->rzsj->item(i,8)->text();
            QDomText text = doc.createTextNode(rankText);
            rank.appendChild(text);

        }

        if(ui->rzsj->item(i,9)!=NULL)
            {
            QString ptyText = ui->rzsj->item(i,9)->text();
            QDomText text = doc.createTextNode(ptyText);
            posttype.appendChild(text);

        }
        if(ui->rzsj->item(i,10)!=NULL)
            {
            QString plText = ui->rzsj->item(i,10)->text();
            QDomText text = doc.createTextNode(plText);
            postlong.appendChild(text);

        }

        jlEmt.appendChild(cadreName);
        jlEmt.appendChild(sex);
        jlEmt.appendChild(idnum);
        jlEmt.appendChild(birth);
        jlEmt.appendChild(old);
        jlEmt.appendChild(unit);
        jlEmt.appendChild(post);
        jlEmt.appendChild(posttime);
        jlEmt.appendChild(rank);
        jlEmt.appendChild(posttype);
        jlEmt.appendChild(postlong);





    }

    //保存
    QTextStream stream(&file); //文本流关联文件
    doc.save(stream, 4); //4 缩进字符
    file.close(); //关闭文件

    QMessageBox::information(this,"提示","导出成功！");

}


void Inform::on_search_clicked()
{


    QString name=ui->nameline->text();
    if (name.isEmpty()) {
        QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("请输入要查询的干部姓名（支持模糊查询）"));

    } else {
        ui->transinfo->setRowCount(0);
        ui->tuixiu->setRowCount(0);

        QSqlQuery query;

        QSqlQuery q;

        QString male=QString::fromUtf8("select CadreName,CadreInfo.IDNum,date_add(Birth,INTERVAL 60 YEAR) ,CONCAT(DATEDIFF(date_add(Birth,INTERVAL 60 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='1' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and DATEDIFF(date_add(Birth,INTERVAL 60 YEAR) ,NOW())<'365' and cadrename like '%%1%'").arg(name);
        QString female=QString::fromUtf8("select CadreName,CadreInfo.IDNum,date_add(Birth,INTERVAL 55 YEAR) ,CONCAT(DATEDIFF(date_add(Birth,INTERVAL 55 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE Sex='0' and CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and DATEDIFF(date_add(Birth,INTERVAL 55 YEAR) ,NOW())<'365' and cadrename like '%%1%'").arg(name);
        q.exec(male);
        while(q.next())
        {
            int rowcount=ui->tuixiu->rowCount();
            ui->tuixiu->insertRow(rowcount);
            ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("退休"));

            QString a=q.value(0).toString();
            ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

            QString b=q.value(2).toString();
            ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

            QString c=q.value(3).toString();
            ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


            QString d=q.value(4).toString();
            ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

            //QString e=q.value(5).toString();
            ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("男"));

            QString f=q.value(5).toString();
            ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

            QString g=q.value(7).toString();
            ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

            QString h=q.value(8).toString();
            ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));

            QString i=q.value(1).toString();
            ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

            QString j=q.value(9).toString();
            ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

            QString k=q.value(10).toString();
            ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));
        }

        query.exec(female);
        while( query.next())
        {

            int rowcount=ui->tuixiu->rowCount();
            ui->tuixiu->insertRow(rowcount);

            ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("退休"));

            QString a=query.value(0).toString();
            ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

            QString i=query.value(1).toString();
            ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

            QString b=query.value(2).toString();
            ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

            QString c=query.value(3).toString();
            ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


            QString d=query.value(4).toString();
            ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

            //QString e=query.value(5).toString();
            ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("女"));

            QString f=query.value(5).toString();
            ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

            QString g=query.value(7).toString();
            ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

            QString h=query.value(8).toString();
            ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));



            QString j=query.value(9).toString();
            ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

            QString k=query.value(10).toString();
            ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));

        }

        QSqlQuery zzm;

        QString zzmale=QString::fromUtf8("select CadreName,CadreInfo.IDNum,date_add(JoinWorkTime,INTERVAL 1 YEAR) ,CONCAT(DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW()),'天'),Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),Sex,unitname,PostName, PostTime,RankName from CadreInfo,Post,unit,RankCode WHERE  CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and CadreInfo.RankID='199' and DATEDIFF(date_add(JoinWorkTime,INTERVAL 1 YEAR) ,NOW())<'90' and cadrename like '%%1%'").arg(name);

        zzm.exec(zzmale);
        while(zzm.next())
        {
            int rowcount=ui->tuixiu->rowCount();
            ui->tuixiu->insertRow(rowcount);
            ui->tuixiu->setItem(rowcount,0,new QTableWidgetItem("转正"));

            QString a=zzm.value(0).toString();
            ui->tuixiu->setItem(rowcount,1,new QTableWidgetItem(a));

            QString b=zzm.value(2).toString();
            ui->tuixiu->setItem(rowcount,3,new QTableWidgetItem(b));

            QString c=zzm.value(3).toString();
            ui->tuixiu->setItem(rowcount,4,new QTableWidgetItem(c));


            QString d=zzm.value(4).toString();
            ui->tuixiu->setItem(rowcount,5,new QTableWidgetItem(d));

            QString f=zzm.value(5).toString();
            ui->tuixiu->setItem(rowcount,6,new QTableWidgetItem(f));

            QString e=zzm.value(6).toString();
            if (e.toInt()==1) {
                ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("男"));

            }
            if (e.toInt()==0) {
                ui->tuixiu->setItem(rowcount,7,new QTableWidgetItem("女"));

            }


            QString g=zzm.value(7).toString();
            ui->tuixiu->setItem(rowcount,8,new QTableWidgetItem(g));

            QString h=zzm.value(8).toString();
            ui->tuixiu->setItem(rowcount,9,new QTableWidgetItem(h));

            QString i=zzm.value(1).toString();
            ui->tuixiu->setItem(rowcount,2,new QTableWidgetItem(i));

            QString j=zzm.value(9).toString();
            ui->tuixiu->setItem(rowcount,10,new QTableWidgetItem(j));

            QString k=zzm.value(10).toString();
            ui->tuixiu->setItem(rowcount,11,new QTableWidgetItem(k));
        }



    int s=ui->tuixiu->rowCount();
    if (s==0) {
        QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("未能查询到该干部提醒信息!"));
        on_ReadTrans_clicked();
    }
    }





}

void Inform::on_tuixiu_cellClicked(int row, int column)
{
   ui->transinfo->setRowCount(0);

    int i=ui->tuixiu->currentRow();
    if (i>-1) {
        QString cadreID = ui->tuixiu->item(i,2)->text();
        QString cadreinfo=QString ("select CadreName,UsedName,CadreInfo.IDNum,Birth,timestampdiff(year,Birth,now()),JoinWorkTime,sex,nation,AdminArea,politic,unitname,PostName,RankName,PostTime,Phone from CadreInfo left join NationCode on CadreInfo.NationID=NationCode.NationID  left join PoliticCode on CadreInfo.PoliticID=PoliticCode.PoliticID  left join RankCode on CadreInfo.RankID=RankCode.RankID  left join AdminAreaCode on CadreInfo.NativePlace=AdminAreaCode.AdminAreaID left join Post on CadreInfo.IDNum=Post.IDNum left join unit on CadreInfo.UnitID=unit.unitid  where   CadreInfo.IDNum='%1'").arg(cadreID);
        QSqlQuery ci;
        ci.exec(cadreinfo);
        while(ci.next())
        {
            int rowcount=ui->transinfo->rowCount();
            ui->transinfo->insertRow(rowcount);
            QString p=ci.value(0).toString();
            ui->transinfo->setItem(rowcount,0,new QTableWidgetItem(p));

            QString o=ci.value(1).toString();
            ui->transinfo->setItem(rowcount,1,new QTableWidgetItem(o));

            QString i=ci.value(2).toString();
            ui->transinfo->setItem(rowcount,2,new QTableWidgetItem(i));

            QString u=ci.value(3).toString();
            ui->transinfo->setItem(rowcount,3,new QTableWidgetItem(u));
            QString a=ci.value(4).toString();
            ui->transinfo->setItem(rowcount,4,new QTableWidgetItem(a));

            QString b=ci.value(5).toString();
            ui->transinfo->setItem(rowcount,5,new QTableWidgetItem(b));



            QString d=ci.value(6).toString();
            if (d.toInt()==1) {
                ui->transinfo->setItem(rowcount,6,new QTableWidgetItem("男"));
            }
            if (d.toInt()==0) {
                ui->transinfo->setItem(rowcount,6,new QTableWidgetItem("女"));
            }


            QString e=ci.value(7).toString();
            ui->transinfo->setItem(rowcount,7,new QTableWidgetItem(e));

            QString f=ci.value(8).toString();
            ui->transinfo->setItem(rowcount,8,new QTableWidgetItem(f));

            QString g=ci.value(9).toString();
            ui->transinfo->setItem(rowcount,9,new QTableWidgetItem(g));

            QString h=ci.value(10).toString();
            ui->transinfo->setItem(rowcount,10,new QTableWidgetItem(h));
            QString j=ci.value(11).toString();
            ui->transinfo->setItem(rowcount,11,new QTableWidgetItem(j));

            QString k=ci.value(12).toString();
            ui->transinfo->setItem(rowcount,12,new QTableWidgetItem(k));

            QString l=ci.value(13).toString();
            ui->transinfo->setItem(rowcount,13,new QTableWidgetItem(l));

            QString m=ci.value(14).toString();
            ui->transinfo->setItem(rowcount,14,new QTableWidgetItem(m));
        }

    }
//    int renshu=ui->cadre->rowCount();
//    ui->renshu->setText(QString::number(renshu));





}

void Inform::on_rzsj_cellClicked(int row, int column)
{



    ui->timeinfo->setRowCount(0);

     int i=ui->rzsj->currentRow();
     if (i>-1) {
         QString cadreID = ui->rzsj->item(i,2)->text();
         QString cadreinfo=QString ("select CadreName,UsedName,CadreInfo.IDNum,Birth,timestampdiff(year,Birth,now()),JoinWorkTime,sex,nation,AdminArea,politic,unitname,PostName,RankName,PostTime,Phone from CadreInfo left join NationCode on CadreInfo.NationID=NationCode.NationID  left join PoliticCode on CadreInfo.PoliticID=PoliticCode.PoliticID  left join RankCode on CadreInfo.RankID=RankCode.RankID  left join AdminAreaCode on CadreInfo.NativePlace=AdminAreaCode.AdminAreaID left join Post on CadreInfo.IDNum=Post.IDNum left join unit on CadreInfo.UnitID=unit.unitid  where   CadreInfo.IDNum='%1'").arg(cadreID);
         QSqlQuery ci;
         ci.exec(cadreinfo);
         while(ci.next())
         {
             int rowcount=ui->timeinfo->rowCount();
             ui->timeinfo->insertRow(rowcount);
             QString p=ci.value(0).toString();
             ui->timeinfo->setItem(rowcount,0,new QTableWidgetItem(p));

             QString o=ci.value(1).toString();
             ui->timeinfo->setItem(rowcount,1,new QTableWidgetItem(o));

             QString i=ci.value(2).toString();
             ui->timeinfo->setItem(rowcount,2,new QTableWidgetItem(i));

             QString u=ci.value(3).toString();
             ui->timeinfo->setItem(rowcount,3,new QTableWidgetItem(u));
             QString a=ci.value(4).toString();
             ui->timeinfo->setItem(rowcount,4,new QTableWidgetItem(a));

             QString b=ci.value(5).toString();
             ui->timeinfo->setItem(rowcount,5,new QTableWidgetItem(b));



             QString d=ci.value(6).toString();
             if (d.toInt()==1) {
                 ui->timeinfo->setItem(rowcount,6,new QTableWidgetItem("男"));
             }
             if (d.toInt()==0) {
                 ui->timeinfo->setItem(rowcount,6,new QTableWidgetItem("女"));
             }


             QString e=ci.value(7).toString();
             ui->timeinfo->setItem(rowcount,7,new QTableWidgetItem(e));

             QString f=ci.value(8).toString();
             ui->timeinfo->setItem(rowcount,8,new QTableWidgetItem(f));

             QString g=ci.value(9).toString();
             ui->timeinfo->setItem(rowcount,9,new QTableWidgetItem(g));

             QString h=ci.value(10).toString();
             ui->timeinfo->setItem(rowcount,10,new QTableWidgetItem(h));
             QString j=ci.value(11).toString();
             ui->timeinfo->setItem(rowcount,11,new QTableWidgetItem(j));

             QString k=ci.value(12).toString();
             ui->timeinfo->setItem(rowcount,12,new QTableWidgetItem(k));

             QString l=ci.value(13).toString();
             ui->timeinfo->setItem(rowcount,13,new QTableWidgetItem(l));

             QString m=ci.value(14).toString();
             ui->timeinfo->setItem(rowcount,14,new QTableWidgetItem(m));
         }

     }
}

void Inform::on_search_2_clicked()
{

    QString name= ui->nameline_2->text();

    if (name.isEmpty()) {
        QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("请输入要查询的干部姓名（支持模糊查询）"));
    } else {
        ui->timeinfo->setRowCount(0);
        ui->rzsj->setRowCount(0);
        QSqlQuery query;
        QString female=QString::fromUtf8("select CadreName,Sex,CadreInfo.IDNum,Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),unitname,PostName, PostTime,RankName,PostCategoryCode.PostCategory,CONCAT(TIMESTAMPDIFF(YEAR,PostTime,NOW()), '年') from CadreInfo,Post,unit,RankCode,PostCategoryCode WHERE  CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and Post.PostCategory=PostCategoryCode.PostCategoryID and TIMESTAMPDIFF(YEAR,PostTime,NOW())>'5' and (Post.PostCategory='21' or Post.PostCategory='11') and cadrename like '%%1%'").arg(name);


        query.exec(female);
        while( query.next())
        {

            int rowcount=ui->rzsj->rowCount();
            ui->rzsj->insertRow(rowcount);
            QString a=query.value(0).toString();
            ui->rzsj->setItem(rowcount,0,new QTableWidgetItem(a));

            QString i=query.value(1).toString();

            if (i.toInt(0)==1) {
                ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("男"));
            }
            else{
                ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("女"));

            }


            QString b=query.value(2).toString();
            ui->rzsj->setItem(rowcount,2,new QTableWidgetItem(b));

            QString c=query.value(3).toString();
            ui->rzsj->setItem(rowcount,3,new QTableWidgetItem(c));


            QString d=query.value(4).toString();
            ui->rzsj->setItem(rowcount,4,new QTableWidgetItem(d));

            QString e=query.value(5).toString();
            ui->rzsj->setItem(rowcount,5,new QTableWidgetItem(e));

            QString f=query.value(6).toString();
            ui->rzsj->setItem(rowcount,6,new QTableWidgetItem(f));

            QString g=query.value(7).toString();
            ui->rzsj->setItem(rowcount,7,new QTableWidgetItem(g));

            QString h=query.value(8).toString();
            ui->rzsj->setItem(rowcount,8,new QTableWidgetItem(h));

            QString j=query.value(9).toString();
            ui->rzsj->setItem(rowcount,9,new QTableWidgetItem(j));

            QString k=query.value(10).toString();
            ui->rzsj->setItem(rowcount,10,new QTableWidgetItem(k));

        }


        QSqlQuery q;
        QString hahaha=QString::fromUtf8("select CadreName,Sex,CadreInfo.IDNum,Birth,TIMESTAMPDIFF(YEAR,Birth,NOW()),unitname,PostName, PostTime,RankName,PostCategoryCode.PostCategory,CONCAT(TIMESTAMPDIFF(YEAR,PostTime,NOW()), '年')from CadreInfo,Post,unit,RankCode,PostCategoryCode WHERE  CadreInfo.IDNum=Post.IDNum and CadreInfo.UnitID=unit.unitid and CadreInfo.RankID=RankCode.RankID and Post.PostCategory=PostCategoryCode.PostCategoryID and TIMESTAMPDIFF(YEAR,PostTime,NOW())>'10' and Post.PostCategory='12' and cadrename like '%%1%'").arg(name);


        q.exec(hahaha);
        while( q.next())
        {

            int rowcount=ui->rzsj->rowCount();
            ui->rzsj->insertRow(rowcount);
            QString a=q.value(0).toString();
            ui->rzsj->setItem(rowcount,0,new QTableWidgetItem(a));

            QString i=q.value(1).toString();

            if (i.toInt(0)==1) {
                ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("男"));
            }
            else{
                ui->rzsj->setItem(rowcount,1,new QTableWidgetItem("女"));

            }


            QString b=q.value(2).toString();
            ui->rzsj->setItem(rowcount,2,new QTableWidgetItem(b));

            QString c=q.value(3).toString();
            ui->rzsj->setItem(rowcount,3,new QTableWidgetItem(c));


            QString d=q.value(4).toString();
            ui->rzsj->setItem(rowcount,4,new QTableWidgetItem(d));

            QString e=q.value(5).toString();
            ui->rzsj->setItem(rowcount,5,new QTableWidgetItem(e));

            QString f=q.value(6).toString();
            ui->rzsj->setItem(rowcount,6,new QTableWidgetItem(f));

            QString g=q.value(7).toString();
            ui->rzsj->setItem(rowcount,7,new QTableWidgetItem(g));

            QString h=q.value(8).toString();
            ui->rzsj->setItem(rowcount,8,new QTableWidgetItem(h));

            QString j=q.value(9).toString();
            ui->rzsj->setItem(rowcount,9,new QTableWidgetItem(j));

            QString k=q.value(10).toString();
            ui->rzsj->setItem(rowcount,10,new QTableWidgetItem(k));

        }
        int st=ui->rzsj->rowCount();
        if (st==0) {
            QMessageBox::critical(this,QString::fromLocal8Bit("提示"),QString::fromLocal8Bit("未能查询到该干部提醒信息!"));
            on_ReadTime_clicked();

        }


    }





}
